In this challenge, your job is to use your data visualisation skills, including aggregation, interactive visualisations, and geospatial analysis, to find properties in the San Francisco market that are viable investment opportunities.
Use the san_francisco_housing.ipynb notebook to visualise and analyse the real-estate data.
Note that this assignment requires you to create a visualisation by using hvPlot and GeoViews. Additionally, you need to read the sfo_neighborhoods_census_data.csv file from the Resources folder into the notebook and create the DataFrame that you’ll use in the analysis.
The main task in this Challenge is to visualise and analyse the real-estate data in your Jupyter notebook. Use the san_francisco_housing.ipynb notebook to complete the following tasks:
Calculate and plot the housing units per year.
Calculate and plot the average prices per square foot.
Compare the average prices by neighbourhood.
Build an interactive neighbourhood map.
Compose your data story.
For this part of the assignment, use numerical and visual aggregation to calculate the number of housing units per year, and then visualise the results as a bar chart. To do so, complete the following steps:
Use the groupby function to group the data by year. Aggregate the results by the mean of the groups.
Use the hvplot function to plot the housing_units_by_year DataFrame as a bar chart. Make the x-axis represent the year and the y-axis represent the housing_units.
Style and format the line plot to ensure a professionally styled visualisation.
Note that your resulting plot should appear similar to the following image:

Answer the following question:
For this part of the assignment, use numerical and visual aggregation to calculate the average prices per square foot, and then visualise the results as a bar chart. To do so, complete the following steps:
Group the data by year, and then average the results. What’s the lowest gross rent that’s reported for the years that the DataFrame includes?
Create a new DataFrame named prices_square_foot_by_year by filtering out the “housing_units” column. The new DataFrame should include the averages per year for only the sale price per square foot and the gross rent.
Use hvPlot to plot the prices_square_foot_by_year DataFrame as a line plot.
Hint This single plot will include lines for both
sale_price_sqr_footandgross_rent.
Style and format the line plot to ensure a professionally styled visualisation.
Note that your resulting plot should appear similar to the following image:

Use both the prices_square_foot_by_year DataFrame and interactive plots to answer the following questions:
Did any year experience a drop in the average sale price per square foot compared to the previous year?
If so, did the gross rent increase or decrease during that year?
For this part of the assignment, use interactive visualisations and widgets to explore the average sale price per square foot by neighbourhood. To do so, complete the following steps:
Create a new DataFrame that groups the original DataFrame by year and neighbourhood. Aggregate the results by the mean of the groups.
Filter out the “housing_units” column to create a DataFrame that includes only the sale_price_sqr_foot and gross_rent averages per year.
Create an interactive line plot with hvPlot that visualises both sale_price_sqr_foot and gross_rent. Set the x-axis parameter to the year (x="year"). Use the groupby parameter to create an interactive widget for neighbourhood.
Style and format the line plot to ensure a professionally styled visualisation.
Note that your resulting plot should appear similar to the following image:

Use the interactive visualisation to answer the following question:
For this part of the assignment, explore the geospatial relationships in the data by using interactive visualisations with hvPlot and GeoViews. To build your map, use the sfo_data_df DataFrame (created during the initial import), which includes the neighbourhood location data with the average prices. To do all this, complete the following steps:
Read the neighbourhood_coordinates.csv file from the Resources folder into the notebook, and create a DataFrame named neighbourhood_locations_df. Be sure to set the index_col of the DataFrame as “Neighbourhood”.
Using the original sfo_data_df Dataframe, create a DataFrame named all_neighbourhood_info_df that groups the data by neighbourhood. Aggregate the results by the mean of the group.
Review the two code cells that concatenate the neighbourhood_locations_df DataFrame with the all_neighbourhood_info_df DataFrame. Note that the first cell uses the Pandas concat function to create a DataFrame named all_neighbourhoods_df. The second cell cleans the data and sets the “Neighbourhood” column. Be sure to run these cells to create the all_neighbourhoods_df DataFrame, which you’ll need to create the geospatial visualisation.
Using hvPlot with GeoViews enabled, create a points plot for the all_neighbourhoods_df DataFrame. Be sure to do the following:
Set the size parameter to “sale_price_sqr_foot”.
Set the color parameter to “gross_rent”.
Set the frame_width parameter to 700.
Set the frame_height parameter to 500.
Include a descriptive title.
Note that your resulting plot should appear similar to the following image:

Use the interactive map to answer the following question:
Based on the visualisations that you created, answer the following questions:
How does the trend in rental income growth compare to the trend in sales prices? Does this same trend hold true for all the neighbourhoods across San Francisco?
What insights can you share with your company about the potential one-click, buy-and-rent strategy that they're pursuing? Do neighbourhoods exist that you would suggest for investment, and why?
# Import the required libraries and dependencies
import pandas as pd
import hvplot.pandas
from pathlib import Path
from panel.interact import interact
import warnings
from shapely.errors import ShapelyDeprecationWarning
# Using the read_csv function and Path module, create a DataFrame
# by importing the sfo_neighbourhoods_census_data.csv file from the Resources folder
sfo_data_df = pd.read_csv(Path("Resources/sfo_neighbourhoods_census_data.csv"))
# Review the first and last five rows of the DataFrame
sfo_data_df.head()
| year | neighbourhood | sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|---|---|
| 0 | 2010 | Alamo Square | 291.182945 | 372560 | 1239 |
| 1 | 2010 | Anza Vista | 267.932583 | 372560 | 1239 |
| 2 | 2010 | Bayview | 170.098665 | 372560 | 1239 |
| 3 | 2010 | Buena Vista Park | 347.394919 | 372560 | 1239 |
| 4 | 2010 | Central Richmond | 319.027623 | 372560 | 1239 |
# Review the last five rows of the DataFrame
sfo_data_df.tail()
| year | neighbourhood | sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|---|---|
| 392 | 2016 | Telegraph Hill | 903.049771 | 384242 | 4390 |
| 393 | 2016 | Twin Peaks | 970.085470 | 384242 | 4390 |
| 394 | 2016 | Van Ness/ Civic Center | 552.602567 | 384242 | 4390 |
| 395 | 2016 | Visitacion Valley | 328.319007 | 384242 | 4390 |
| 396 | 2016 | Westwood Park | 631.195426 | 384242 | 4390 |
For this part of the assignment, use numerical and visual aggregation to calculate the number of housing units per year, and then visualise the results as a bar chart. To do so, complete the following steps:
Use the groupby function to group the data by year. Aggregate the results by the mean of the groups.
Use the hvplot function to plot the housing_units_by_year DataFrame as a bar chart. Make the x-axis represent the year and the y-axis represent the housing_units.
Style and format the line plot to ensure a professionally styled visualisation.
Note that your resulting plot should appear similar to the following image:

Answer the following question:
groupby function to group the data by year. Aggregate the results by the mean of the groups.¶# Create a numerical aggregation that groups the data by the year and then averages the results.
housing_by_year = sfo_data_df.groupby("year").mean()
housing_units_by_year = housing_by_year[["housing_units"]]
# Review the DataFrame
housing_units_by_year
| housing_units | |
|---|---|
| year | |
| 2010 | 372560.0 |
| 2011 | 374507.0 |
| 2012 | 376454.0 |
| 2013 | 378401.0 |
| 2014 | 380348.0 |
| 2015 | 382295.0 |
| 2016 | 384242.0 |
# Create a visual aggregation explore the housing units by year
housing_units_by_year.hvplot.bar(x="year",y="housing_units", xlabel="Year", ylabel="Housing Units", height=600, width=1200, ylim=(365000,None), label="Housing Units in San Francisco from 2010 to 2016").opts(yformatter="%.0f")
Question: What is the overall trend in housing_units over the period being analysed?
Answer: The average number of housing units in San Francisco from 2010 to 2016 is increasing trend year by year.
For this part of the assignment, use numerical and visual aggregation to calculate the average prices per square foot, and then visualise the results as a bar chart. To do so, complete the following steps:
Group the data by year, and then average the results. What’s the lowest gross rent that’s reported for the years that the DataFrame includes?
Create a new DataFrame named prices_square_foot_by_year by filtering out the “housing_units” column. The new DataFrame should include the averages per year for only the sale price per square foot and the gross rent.
Use hvPlot to plot the prices_square_foot_by_year DataFrame as a line plot.
Hint This single plot will include lines for both
sale_price_sqr_footandgross_rent.
Style and format the line plot to ensure a professionally styled visualisation.
Note that your resulting plot should appear similar to the following image:

Use both the prices_square_foot_by_year DataFrame and interactive plots to answer the following questions:
Did any year experience a drop in the average sale price per square foot compared to the previous year?
If so, did the gross rent increase or decrease during that year?
# Create a numerical aggregation by grouping the data by year and averaging the results
prices_square_foot_by_year = housing_by_year[["sale_price_sqr_foot"]]
# Review the resulting DataFrame
prices_square_foot_by_year.head()
| sale_price_sqr_foot | |
|---|---|
| year | |
| 2010 | 369.344353 |
| 2011 | 341.903429 |
| 2012 | 399.389968 |
| 2013 | 483.600304 |
| 2014 | 556.277273 |
Question: What is the lowest gross rent reported for the years included in the DataFrame?
Answer: # The lowest rent is $1,239.00 in 2010
prices_square_foot_by_year by filtering out the “housing_units” column. The new DataFrame should include the averages per year for only the sale price per square foot and the gross rent.¶# Filter out the housing_units column, creating a new DataFrame
# Keep only sale_price_sqr_foot and gross_rent averages per year
prices_square_foot_by_year = housing_by_year[["sale_price_sqr_foot","gross_rent"]]
# Review the DataFrame
prices_square_foot_by_year.head()
| sale_price_sqr_foot | gross_rent | |
|---|---|---|
| year | ||
| 2010 | 369.344353 | 1239.0 |
| 2011 | 341.903429 | 1530.0 |
| 2012 | 399.389968 | 2324.0 |
| 2013 | 483.600304 | 2971.0 |
| 2014 | 556.277273 | 3528.0 |
# Plot prices_square_foot_by_year.
# Inclued labels for the x- and y-axes, and a title.
prices_square_foot_by_year.hvplot.line(xlabel="Year",ylabel="Gross Rent/Sale Price Per Square Foot", width=1200, height=500)
prices_square_foot_by_year DataFrame and interactive plots to answer the following questions:¶Question: Did any year experience a drop in the average sale price per square foot compared to the previous year?
Answer: The average sale priceper square steadly increased year by year. Thus there is no year that the average sale price dropped.
Question: If so, did the gross rent increase or decrease during that year?
Answer: The average gross rent is also increased year by year.
For this part of the assignment, use interactive visualisations and widgets to explore the average sale price per square foot by neighbourhood. To do so, complete the following steps:
Create a new DataFrame that groups the original DataFrame by year and neighbourhood. Aggregate the results by the mean of the groups.
Filter out the “housing_units” column to create a DataFrame that includes only the sale_price_sqr_foot and gross_rent averages per year.
Create an interactive line plot with hvPlot that visualises both sale_price_sqr_foot and gross_rent. Set the x-axis parameter to the year (x="year"). Use the groupby parameter to create an interactive widget for neighbourhood.
Style and format the line plot to ensure a professionally styled visualisation.
Note that your resulting plot should appear similar to the following image:

Use the interactive visualisation to answer the following question:
mean of the groups.¶# Group by year and neighbourhood and then create a new dataframe of the mean values
prices_by_year_by_neighbourhood = sfo_data_df.groupby(["year","neighbourhood"]).mean().reset_index()
# Review the DataFrame
prices_by_year_by_neighbourhood.head()
| year | neighbourhood | sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|---|---|
| 0 | 2010 | Alamo Square | 291.182945 | 372560.0 | 1239.0 |
| 1 | 2010 | Anza Vista | 267.932583 | 372560.0 | 1239.0 |
| 2 | 2010 | Bayview | 170.098665 | 372560.0 | 1239.0 |
| 3 | 2010 | Buena Vista Park | 347.394919 | 372560.0 | 1239.0 |
| 4 | 2010 | Central Richmond | 319.027623 | 372560.0 | 1239.0 |
sale_price_sqr_foot and gross_rent averages per year.¶# Filter out the housing_units
prices_by_year_by_neighbourhood = prices_by_year_by_neighbourhood.drop(columns=["housing_units"])
# Review the first five rows of the DataFrame
prices_by_year_by_neighbourhood.head()
| year | neighbourhood | sale_price_sqr_foot | gross_rent | |
|---|---|---|---|---|
| 0 | 2010 | Alamo Square | 291.182945 | 1239.0 |
| 1 | 2010 | Anza Vista | 267.932583 | 1239.0 |
| 2 | 2010 | Bayview | 170.098665 | 1239.0 |
| 3 | 2010 | Buena Vista Park | 347.394919 | 1239.0 |
| 4 | 2010 | Central Richmond | 319.027623 | 1239.0 |
# Review the last five rows of the DataFrame
prices_by_year_by_neighbourhood.tail()
| year | neighbourhood | sale_price_sqr_foot | gross_rent | |
|---|---|---|---|---|
| 392 | 2016 | Telegraph Hill | 903.049771 | 4390.0 |
| 393 | 2016 | Twin Peaks | 970.085470 | 4390.0 |
| 394 | 2016 | Van Ness/ Civic Center | 552.602567 | 4390.0 |
| 395 | 2016 | Visitacion Valley | 328.319007 | 4390.0 |
| 396 | 2016 | Westwood Park | 631.195426 | 4390.0 |
sale_price_sqr_foot and gross_rent. Set the x-axis parameter to the year (x="year"). Use the groupby parameter to create an interactive widget for neighbourhood.¶# Use hvplot to create an interactive line plot of the average price per square foot
# The plot should have a dropdown selector for the neighbourhood
# Make dropdown menu with neighbourhood
list_of_neigbourhood = prices_by_year_by_neighbourhood["neighbourhood"].unique()
# This function create a list for dropdown menu and returns plot
def choose_neighbourhood(neighbourhood):
selected_neighbourhood = prices_by_year_by_neighbourhood.loc[prices_by_year_by_neighbourhood["neighbourhood"] == neighbourhood]
# Plot prices_square_foot_by_year with chosen neighbourhood.
# Inclued labels for the x- and y-axes, and a title.
selected_neighbourhood_plot = selected_neighbourhood.hvplot.line(x="year",xlabel="Year", ylabel="Gross Rent/Sale Price Per Square Foot", width=1200, height=500, title="Sale Price Per Squar Foot and Average Gross Rent - 2010-2016 - By Neighborhood")
return selected_neighbourhood_plot
# This will show dropdown menu with plot line
interact(choose_neighbourhood, neighbourhood=list_of_neigbourhood)
Question: For the Anza Vista neighbourhood, is the average sale price per square foot for 2016 more or less than the price that’s listed for 2012?
Answer: By observing the sale price per square food in the plot, the average price per square foot for 2016 has been decreased compared to the year 2012
For this part of the assignment, explore the geospatial relationships in the data by using interactive visualisations with hvPlot and GeoViews. To build your map, use the sfo_data_df DataFrame (created during the initial import), which includes the neighbourhood location data with the average prices. To do all this, complete the following steps:
Read the neighbourhood_coordinates.csv file from the Resources folder into the notebook, and create a DataFrame named neighbourhood_locations_df. Be sure to set the index_col of the DataFrame as “Neighbourhood”.
Using the original sfo_data_df Dataframe, create a DataFrame named all_neighbourhood_info_df that groups the data by neighbourhood. Aggregate the results by the mean of the group.
Review the two code cells that concatenate the neighbourhood_locations_df DataFrame with the all_neighbourhood_info_df DataFrame. Note that the first cell uses the Pandas concat function to create a DataFrame named all_neighbourhoods_df. The second cell cleans the data and sets the “Neighbourhood” column. Be sure to run these cells to create the all_neighbourhoods_df DataFrame, which you’ll need to create the geospatial visualisation.
Using hvPlot with GeoViews enabled, create a points plot for the all_neighbourhoods_df DataFrame. Be sure to do the following:
Set the size parameter to “sale_price_sqr_foot”.
Set the color parameter to “gross_rent”.
Set the frame_width parameter to 700.
Set the frame_height parameter to 500.
Include a descriptive title.
Note that your resulting plot should appear similar to the following image:

Use the interactive map to answer the following question:
neighbourhood_coordinates.csv file from the Resources folder into the notebook, and create a DataFrame named neighbourhood_locations_df. Be sure to set the index_col of the DataFrame as “Neighbourhood”.¶# Load neighbourhoods coordinates data
neighbourhood_locations_df = pd.read_csv(Path("Resources/neighbourhoods_coordinates.csv")).set_index("Neighbourhood")
# Review the DataFrame
neighbourhood_locations_df.head()
| Lat | Lon | |
|---|---|---|
| Neighbourhood | ||
| Alamo Square | 37.791012 | -122.402100 |
| Anza Vista | 37.779598 | -122.443451 |
| Bayview | 37.734670 | -122.401060 |
| Bayview Heights | 37.728740 | -122.410980 |
| Bernal Heights | 37.728630 | -122.443050 |
sfo_data_df Dataframe, create a DataFrame named all_neighbourhood_info_df that groups the data by neighbourhood. Aggregate the results by the mean of the group.¶# Calculate the mean values for each neighbourhood
all_neighbourhood_info_df = sfo_data_df.groupby("neighbourhood").mean().round(2).drop(columns='year')
# Review the resulting DataFrame
all_neighbourhood_info_df.head()
| sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|
| neighbourhood | |||
| Alamo Square | 366.02 | 378401.0 | 2817.29 |
| Anza Vista | 373.38 | 379050.0 | 3031.83 |
| Bayview | 204.59 | 376454.0 | 2318.40 |
| Bayview Heights | 590.79 | 382295.0 | 3739.00 |
| Bernal Heights | 576.75 | 379374.5 | 3080.33 |
neighbourhood_locations_df DataFrame with the all_neighbourhood_info_df DataFrame.¶Note that the first cell uses the Pandas concat function to create a DataFrame named all_neighbourhoods_df.
The second cell cleans the data and sets the “Neighbourhood” column.
Be sure to run these cells to create the all_neighbourhoods_df DataFrame, which you’ll need to create the geospatial visualisation.
# Using the Pandas `concat` function, join the
# neighbourhood_locations_df and the all_neighbourhood_info_df DataFrame
# The axis of the concatenation is "columns".
# The concat function will automatially combine columns with
# identical information, while keeping the additional columns.
all_neighbourhoods_df = pd.concat(
[neighbourhood_locations_df, all_neighbourhood_info_df],
axis="columns",
sort=False
)
# Review the resulting DataFrame
display(all_neighbourhoods_df.head())
display(all_neighbourhoods_df.tail())
| Lat | Lon | sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|---|---|
| Alamo Square | 37.791012 | -122.402100 | 366.02 | 378401.0 | 2817.29 |
| Anza Vista | 37.779598 | -122.443451 | 373.38 | 379050.0 | 3031.83 |
| Bayview | 37.734670 | -122.401060 | 204.59 | 376454.0 | 2318.40 |
| Bayview Heights | 37.728740 | -122.410980 | 590.79 | 382295.0 | 3739.00 |
| Bernal Heights | 37.728630 | -122.443050 | NaN | NaN | NaN |
| Lat | Lon | sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|---|---|
| Yerba Buena | 37.79298 | -122.39636 | 576.71 | 377427.5 | 2555.17 |
| Bernal Heights | NaN | NaN | 576.75 | 379374.5 | 3080.33 |
| Downtown | NaN | NaN | 391.43 | 378401.0 | 2817.29 |
| Ingleside | NaN | NaN | 367.90 | 377427.5 | 2509.00 |
| Outer Richmond | NaN | NaN | 473.90 | 378401.0 | 2817.29 |
# Call the dropna function to remove any neighbourhoods that do not have data
all_neighbourhoods_df = all_neighbourhoods_df.reset_index().dropna()
# Rename the "index" column as "Neighbourhood" for use in the Visualisation
all_neighbourhoods_df = all_neighbourhoods_df.rename(columns={"index": "Neighbourhood"})
# Review the resulting DataFrame
display(all_neighbourhoods_df.head())
display(all_neighbourhoods_df.tail())
| Neighbourhood | Lat | Lon | sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|---|---|---|
| 0 | Alamo Square | 37.791012 | -122.402100 | 366.02 | 378401.0 | 2817.29 |
| 1 | Anza Vista | 37.779598 | -122.443451 | 373.38 | 379050.0 | 3031.83 |
| 2 | Bayview | 37.734670 | -122.401060 | 204.59 | 376454.0 | 2318.40 |
| 3 | Bayview Heights | 37.728740 | -122.410980 | 590.79 | 382295.0 | 3739.00 |
| 5 | Buena Vista Park | 37.768160 | -122.439330 | 452.68 | 378076.5 | 2698.83 |
| Neighbourhood | Lat | Lon | sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|---|---|---|
| 68 | West Portal | 37.74026 | -122.463880 | 498.49 | 376940.75 | 2515.50 |
| 69 | Western Addition | 37.79298 | -122.435790 | 307.56 | 377427.50 | 2555.17 |
| 70 | Westwood Highlands | 37.73470 | -122.456854 | 533.70 | 376454.00 | 2250.50 |
| 71 | Westwood Park | 37.73415 | -122.457000 | 687.09 | 382295.00 | 3959.00 |
| 72 | Yerba Buena | 37.79298 | -122.396360 | 576.71 | 377427.50 | 2555.17 |
points plot for the all_neighbourhoods_df DataFrame. Be sure to do the following:¶geo parameter to True.size parameter to “sale_price_sqr_foot”.color parameter to “gross_rent”.frame_width parameter to 700.frame_height parameter to 500.# This suppresses warning message
warnings.filterwarnings("ignore", category=ShapelyDeprecationWarning)
# Create a plot to analyse neighbourhood info
all_neighbourhoods_df.hvplot.points('Lon','Lat',xlabel='Longitude', ylabel='Latitude',geo=True,color='gross_rent',size='sale_price_sqr_foot',hover_cols='Neighbourhood',tiles='OSM',frame_width=700, frame_height=500,
title='Sale Price and Rent of Real Estate in neighbourhoods of San Francisco',).opts(yformatter='%.0f')
Question: Which neighbourhood has the highest gross rent, and which has the highest sale price per square foot?
Answer: The hightest gross rent neibhbourhood is Westwood Park, whose latitude is 37.73415, and longitude is -122.457. The gross rent is $3959.00.
Answer: The hightest sale price per square is Union Square District, whose latitude is 37.79101, and longitude is -122.4021. The sale price per square foot is $903.99.
Based on the visualisations that you created, answer the following questions:
Question: How does the trend in rental income growth compare to the trend in sales prices? Does this same trend hold true for all the neighbourhoods across San Francisco?
Answer: The gross rent is steadly growing year by year. However, sales price per square foot varies depending on the area across San Francisco.
Question: What insights can you share with your company about the potential one-click, buy-and-rent strategy that they're pursuing? Do neighbourhoods exist that you would suggest for investment, and why?
Answer: The one-click idea is revolutionary, and it will fit ever fast-paced society. However, realistically to do it, we need more data to determine it, such as neighbourhood safety, transportation accessibility, etc. If we can incorporate these multi-dimensional data as interactive tools, we could make it happen. In terms of investment suggestion, the area where latitude is 37.7341, and longitude is -122.4570 has a potential for growth because of the concentration of areas for high gross rent.